package com.example.houseitemmanagebackend.util;

import com.example.houseitemmanagebackend.exception.BusinessException;
import com.example.houseitemmanagebackend.vo.ItemVO;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ByteArrayResource;
import org.springframework.core.io.Resource;

import java.io.BufferedReader;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

public class ExcelUtil {

    private static final DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd");

    /**
     * 读取Excel文件
     */
    public static List<Map<String, Object>> readExcelFile(InputStream inputStream) throws IOException {
        List<Map<String, Object>> data = new ArrayList<>();

        try (Workbook workbook = WorkbookFactory.create(inputStream)) {
            Sheet sheet = workbook.getSheetAt(0);

            // 获取表头行
            Row headerRow = sheet.getRow(0);
            if (headerRow == null) {
                throw new BusinessException(400, "Excel文件格式错误：缺少表头行");
            }

            // 解析表头
            List<String> headers = new ArrayList<>();
            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                Cell cell = headerRow.getCell(i);
                if (cell != null) {
                    headers.add(cell.getStringCellValue().trim());
                } else {
                    headers.add("");
                }
            }

            // 检查必要的列是否存在
            validateHeaders(headers);

            // 读取数据行
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null)
                    continue;

                Map<String, Object> rowData = new HashMap<>();
                boolean hasData = false;

                for (int j = 0; j < headers.size(); j++) {
                    if (j >= row.getLastCellNum())
                        continue;

                    Cell cell = row.getCell(j);
                    String header = headers.get(j);

                    if (header.isEmpty())
                        continue;

                    Object value = getCellValue(cell);
                    if (value != null) {
                        rowData.put(header, value);
                        hasData = true;
                    }
                }

                if (hasData) {
                    data.add(rowData);
                }
            }
        }

        return data;
    }

    /**
     * 读取CSV文件
     */
    public static List<Map<String, Object>> readCsvFile(InputStream inputStream) throws IOException {
        List<Map<String, Object>> data = new ArrayList<>();
        try (BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream, StandardCharsets.UTF_8))) {
            // 读取表头
            String headerLine = reader.readLine();
            if (headerLine == null) {
                throw new BusinessException(400, "CSV文件格式错误：缺少表头行");
            }

            // 解析表头
            List<String> headers = Arrays.asList(headerLine.split(","));
            headers = headers.stream().map(String::trim).collect(Collectors.toList());

            // 检查必要的列是否存在
            validateHeaders(headers);

            // 读取数据行
            String line;
            while ((line = reader.readLine()) != null) {
                if (line.trim().isEmpty())
                    continue;

                String[] values = line.split(",");
                Map<String, Object> rowData = new HashMap<>();
                boolean hasData = false;

                for (int i = 0; i < headers.size(); i++) {
                    if (i >= values.length)
                        continue;

                    String header = headers.get(i);
                    String value = values[i].trim();

                    if (!header.isEmpty() && !value.isEmpty()) {
                        rowData.put(header, value);
                        hasData = true;
                    }
                }

                if (hasData) {
                    data.add(rowData);
                }
            }
        }

        return data;
    }

    /**
     * 验证表头是否包含必要的列
     */
    private static void validateHeaders(List<String> headers) {
        // 定义必要的列
        List<String> requiredHeaders = Arrays.asList("名称", "分类", "位置", "数量");

        // 检查必要的列是否存在
        List<String> missingHeaders = requiredHeaders.stream()
                .filter(header -> !headers.contains(header))
                .collect(Collectors.toList());

        if (!missingHeaders.isEmpty()) {
            throw new BusinessException(400, "文件格式错误：缺少必要的列 " + String.join(", ", missingHeaders));
        }
    }

    /**
     * 获取单元格的值
     */
    private static Object getCellValue(Cell cell) {
        if (cell == null)
            return null;

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue();
                } else {
                    return cell.getNumericCellValue();
                }
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case FORMULA:
                switch (cell.getCachedFormulaResultType()) {
                    case STRING:
                        return cell.getStringCellValue().trim();
                    case NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            return cell.getDateCellValue();
                        } else {
                            return cell.getNumericCellValue();
                        }
                    default:
                        return null;
                }
            default:
                return null;
        }
    }

    /**
     * 导出数据到Excel
     */
    public static Resource exportToExcel(List<ItemVO> items, Boolean includeStock, Boolean includeExpired,
            Boolean includeCategories) throws IOException {
        // 创建工作簿和工作表
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("物品列表");

            // 创建表头样式
            CellStyle headerStyle = workbook.createCellStyle();
            Font headerFont = workbook.createFont();
            headerFont.setBold(true);
            headerStyle.setFont(headerFont);

            // 创建表头
            Row headerRow = sheet.createRow(0);
            List<String> headers = getExportHeaders(includeStock, includeExpired, includeCategories);

            for (int i = 0; i < headers.size(); i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers.get(i));
                cell.setCellStyle(headerStyle);
            }

            // 写入数据
            int rowNum = 1;
            for (ItemVO item : items) {
                Row row = sheet.createRow(rowNum++);
                int colNum = 0;

                // 基本信息
                row.createCell(colNum++).setCellValue(item.getName());
                row.createCell(colNum++).setCellValue(item.getLocationName());

                // 分类信息（如果需要）
                if (includeCategories) {
                    row.createCell(colNum++).setCellValue(item.getCategoryName());
                }

                // 库存信息（如果需要）
                if (includeStock) {
                    Cell quantityCell = row.createCell(colNum++);
                    if (item.getQuantity() != null) {
                        quantityCell.setCellValue(item.getQuantity());
                    }

                    if (item.getIsConsumable() != null && item.getIsConsumable() == 1) {
                        row.createCell(colNum++).setCellValue("是");
                    } else {
                        row.createCell(colNum++).setCellValue("否");
                    }
                }

                // 购买信息
                Cell purchaseDateCell = row.createCell(colNum++);
                if (item.getPurchaseDate() != null) {
                    purchaseDateCell.setCellValue(item.getPurchaseDate().format(DATE_FORMATTER));
                }

                Cell priceCell = row.createCell(colNum++);
                if (item.getPrice() != null) {
                    priceCell.setCellValue(item.getPrice().doubleValue());
                }

                // 过期信息（如果需要）
                if (includeExpired) {
                    Cell expireDateCell = row.createCell(colNum++);
                    if (item.getExpiryDate() != null) {
                        expireDateCell.setCellValue(item.getExpiryDate().format(DATE_FORMATTER));
                    }
                }

                // 备注
                Cell notesCell = row.createCell(colNum);
                if (item.getNotes() != null) {
                    notesCell.setCellValue(item.getNotes());
                }
            }

            // 调整列宽
            for (int i = 0; i < headers.size(); i++) {
                sheet.autoSizeColumn(i);
            }

            // 将工作簿写入临时文件
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            workbook.write(outputStream);

            return new ByteArrayResource(outputStream.toByteArray());
        }
    }

    /**
     * 导出数据到CSV
     */
    public static Resource exportToCsv(List<ItemVO> items, Boolean includeStock, Boolean includeExpired,
            Boolean includeCategories) throws IOException {
        // 获取表头
        List<String> headers = getExportHeaders(includeStock, includeExpired, includeCategories);

        // 创建CSV数据
        StringBuilder csv = new StringBuilder();

        // 添加表头
        csv.append(String.join(",", headers)).append("\n");

        // 添加数据行
        for (ItemVO item : items) {
            List<String> rowData = new ArrayList<>();

            // 基本信息
            rowData.add(csvEscape(item.getName()));
            rowData.add(csvEscape(item.getLocationName()));

            // 分类信息（如果需要）
            if (includeCategories) {
                rowData.add(csvEscape(item.getCategoryName()));
            }

            // 库存信息（如果需要）
            if (includeStock) {
                rowData.add(item.getQuantity() != null ? item.getQuantity().toString() : "");
                rowData.add(item.getIsConsumable() != null && item.getIsConsumable() == 1 ? "是" : "否");
            }

            // 购买信息
            rowData.add(item.getPurchaseDate() != null ? item.getPurchaseDate().format(DATE_FORMATTER) : "");
            rowData.add(item.getPrice() != null ? item.getPrice().toString() : "");

            // 过期信息（如果需要）
            if (includeExpired) {
                rowData.add(item.getExpiryDate() != null ? item.getExpiryDate().format(DATE_FORMATTER) : "");
            }

            // 备注
            rowData.add(csvEscape(item.getNotes()));

            csv.append(String.join(",", rowData)).append("\n");
        }

        // 返回CSV数据
        byte[] bytes = csv.toString().getBytes(StandardCharsets.UTF_8);
        return new ByteArrayResource(bytes);
    }

    /**
     * 获取导出表头
     */
    private static List<String> getExportHeaders(Boolean includeStock, Boolean includeExpired,
            Boolean includeCategories) {
        List<String> headers = new ArrayList<>();

        // 基本信息
        headers.add("名称");
        headers.add("位置");

        // 分类信息（如果需要）
        if (includeCategories) {
            headers.add("分类");
        }

        // 库存信息（如果需要）
        if (includeStock) {
            headers.add("数量");
            headers.add("是否消耗品");
        }

        // 购买信息
        headers.add("购买日期");
        headers.add("价格");

        // 过期信息（如果需要）
        if (includeExpired) {
            headers.add("过期日期");
        }

        // 备注
        headers.add("备注");

        return headers;
    }

    /**
     * 转义CSV字段
     */
    private static String csvEscape(String value) {
        if (value == null)
            return "";

        // 如果字段包含逗号、引号或换行符，则用引号包围并转义引号
        if (value.contains(",") || value.contains("\"") || value.contains("\n")) {
            return "\"" + value.replace("\"", "\"\"") + "\"";
        }

        return value;
    }
}